﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace syc.Juece.Report
{
    public partial class 月份生产经营计划安排表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected double 套管计划;
        protected double 套管N80计划;
        protected double 油管计划;
        protected double 套管计划产值;
        protected double 套管N80计划产值;
        protected double 油管计划产值;
        protected int year;
        protected int month;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.Month0.SelectedValue = DateTime.Today.Month.ToString();
                this.Year0.Text = DateTime.Today.Year.ToString();
            }
            Button_Query_Click(null, null);
        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/JueCe/模板/产量产值完成情况表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "生产经营计划安排表";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "生产经营计划安排表";
            hssfworkbook.SummaryInformation = si;
        }
        protected void WriteToFile()
        {
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + DateTime.Today.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }
        protected void Button_Save_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("生产经营计划安排表");
            Button_Query_Click(null, null);
            sheet1.GetRow(0).GetCell(0).SetCellValue(this.Year0.Text + "年" + this.Month0.Text + "月份生产经营计划安排表");
            sheet1.GetRow(1).GetCell(4).SetCellValue(套管计划);
            sheet1.GetRow(2).GetCell(4).SetCellValue(套管N80计划);
            sheet1.GetRow(3).GetCell(4).SetCellValue(油管计划);
            sheet1.GetRow(4).GetCell(4).SetCellValue(套管计划产值.ToString("0.00"));
            sheet1.GetRow(5).GetCell(4).SetCellValue(套管N80计划产值.ToString("0.00"));
            sheet1.GetRow(6).GetCell(4).SetCellValue(油管计划产值.ToString("0.00"));  
            WriteToFile();
            Response.End();
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            year = int.Parse(this.Year0.Text);
            month = int.Parse(this.Month0.Text);
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            //获得年计划
            Cmd.CommandText = @"
            declare @套管计划 float
            declare @套管N80计划 float
            declare @油管计划 float
            declare @套管计划产值 float
            declare @套管N80计划产值 float
            declare @油管计划产值 float
            select  @套管计划=isnull(sum(套管),0),@套管N80计划=0,@油管计划=isnull(sum(油管),0) from 生产计划2 where 年度='" + year + "' and 月份='" + month + "' select  @套管计划产值=isnull(sum(计划产值),0) from 工业总产值数据  where 年份='" + year + "' and 月份='" + month + "' and 产品类型ID=2 select  @油管计划产值=isnull(sum(计划产值),0) from 工业总产值数据  where 年份='" + year + "' and 月份='" + month + "' and 产品类型ID=1 select  @套管N80计划产值=isnull(sum(计划产值),0) from 工业总产值数据  where 年份='" + year + "' and 月份='" + month + "' and 产品类型ID=4 select @套管计划,@油管计划,@套管计划产值,@油管计划产值,@套管N80计划,@套管N80计划产值";
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                套管计划 = Reader.GetDouble(0);
                油管计划 = Reader.GetDouble(1);
                套管计划产值 = Reader.GetDouble(2);
                油管计划产值 = Reader.GetDouble(3);
                套管N80计划 = 0;
                套管N80计划产值 = Reader.GetDouble(5);
            }
         
         
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }       
    }
}
